package jdbc;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;

import java.util.List;

@SpringJUnitConfig(locations = "classpath:beans.xml")
public class jdbcTemplateTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    //查询：返回对象
    @Test
    public void testSelectObject() {
        //手动封装
//        String sql = "select * from t_emp where id=?";
//        Emp empResult = jdbcTemplate.queryForObject(sql,
//                (rs,rowNum) -> {
//                    Emp emp = new Emp();
//                    emp.setName(rs.getString("name"));
//                    emp.setAge(rs.getInt("age"));
//                    emp.setSex(rs.getString("sex"));
//                    return emp;
//                },1);
//        System.out.println(empResult);

        //自动  查询
        String sql = "select * from t_emp where id=?";
        Emp emp = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(Emp.class),1);
        System.out.println(emp);
    }


    //查询：返回list集合
    @Test
    public void testSelectList(){
        String sql = "select * from t_emp";
        List<Emp> list = jdbcTemplate.query(sql,
                new BeanPropertyRowMapper<>(Emp.class));
        System.out.println(list);
    }


    //查询：返回单个值
    @Test
    public void testSelectValue(){
        String sql = "select count(*) from t_emp";
        //查询表内记录数
        Integer count = jdbcTemplate.queryForObject(sql,Integer.class);
        System.out.println(count);
    }


    //对数据库添加 修改 删除
    @Test
    public void testUpdate() {
        //对数据库添加
        //编写sql语句
        String sql = "insert into t_emp() values(NULL,?,?,?)";
        //调用jdbcTemplate的方法
        Object[] params = {"李", 11, "男"};
        int rows = jdbcTemplate.update(sql, params);
        System.out.println(rows);

        //对数据库修改
        //编写sql语句
        String sql2 = "update t_emp() set name=? where id=?";
        //调用jdbcTemplate的方法
        int rows2 = jdbcTemplate.update(sql2, "刘", 3);
        System.out.println(rows2);

        //对数据库修改
        //编写sql语句
        String sql3 = "delete from t_emp() where id=?";
        //调用jdbcTemplate的方法
        int rows3 = jdbcTemplate.update(sql3, 3);
        System.out.println(rows3);
    }
}
